﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human
{
	public class Baocao_chamcong : System.Windows.Forms.Form
	{
		private AccessData mDB =new AccessData();
        string sql = "",sql2="", tungay = "", denngay = "",ngay="",user="",nhomca="";
        int tn_thcc = 0, dn_thcc = 0,p_thang=0,p_nam=0;
        bool bvantay = false;
        private DataSet ds_nv = new DataSet();
        private DataSet ds = new DataSet();
        private DataSet ds_dmqd = new DataSet();
        private DataSet ds_ca = new DataSet();
        private DataSet ds_ldo = new DataSet();
        Language lan = new Language();
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Button btn_chon;
		private System.Windows.Forms.Button btn_thoat;
		private System.Windows.Forms.TextBox txtthang;
		private System.Windows.Forms.TextBox txtnam;
		private string thang="",nam="",s_mmyy="",s_bophan="",user_dn="",mmyy_tr="";
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.ComboBox cbobophan;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton radBC;
		private System.Windows.Forms.RadioButton radKhac;
        private Button btn_exel;
		private System.ComponentModel.Container components = null;

		public Baocao_chamcong(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Baocao_chamcong));
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.btn_chon = new System.Windows.Forms.Button();
            this.btn_thoat = new System.Windows.Forms.Button();
            this.txtthang = new System.Windows.Forms.TextBox();
            this.txtnam = new System.Windows.Forms.TextBox();
            this.label3 = new System.Windows.Forms.Label();
            this.cbobophan = new System.Windows.Forms.ComboBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.radKhac = new System.Windows.Forms.RadioButton();
            this.radBC = new System.Windows.Forms.RadioButton();
            this.btn_exel = new System.Windows.Forms.Button();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.ForeColor = System.Drawing.Color.DarkBlue;
            this.label1.Location = new System.Drawing.Point(22, 17);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(56, 23);
            this.label1.TabIndex = 4;
            this.label1.Text = "Tháng :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.ForeColor = System.Drawing.Color.DarkBlue;
            this.label2.Location = new System.Drawing.Point(142, 17);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(40, 23);
            this.label2.TabIndex = 5;
            this.label2.Text = "Năm :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // btn_chon
            // 
            this.btn_chon.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_chon.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_chon.Image = global::Human.Properties.Resources.t_search;
            this.btn_chon.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_chon.Location = new System.Drawing.Point(25, 144);
            this.btn_chon.Name = "btn_chon";
            this.btn_chon.Size = new System.Drawing.Size(60, 28);
            this.btn_chon.TabIndex = 5;
            this.btn_chon.Text = "&Chọn";
            this.btn_chon.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_chon.Click += new System.EventHandler(this.btn_chon_Click);
            // 
            // btn_thoat
            // 
            this.btn_thoat.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_thoat.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_thoat.Image = global::Human.Properties.Resources.close_r;
            this.btn_thoat.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_thoat.Location = new System.Drawing.Point(157, 144);
            this.btn_thoat.Name = "btn_thoat";
            this.btn_thoat.Size = new System.Drawing.Size(80, 28);
            this.btn_thoat.TabIndex = 7;
            this.btn_thoat.Text = "&Kết thúc";
            this.btn_thoat.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_thoat.Click += new System.EventHandler(this.btn_thoat_Click);
            // 
            // txtthang
            // 
            this.txtthang.Location = new System.Drawing.Point(84, 17);
            this.txtthang.Name = "txtthang";
            this.txtthang.Size = new System.Drawing.Size(40, 21);
            this.txtthang.TabIndex = 0;
            this.txtthang.Validated += new System.EventHandler(this.txtthang_Validated);
            this.txtthang.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtthang_KeyDown);
            // 
            // txtnam
            // 
            this.txtnam.Location = new System.Drawing.Point(190, 17);
            this.txtnam.Name = "txtnam";
            this.txtnam.Size = new System.Drawing.Size(56, 21);
            this.txtnam.TabIndex = 1;
            this.txtnam.Validated += new System.EventHandler(this.txtnam_Validated);
            this.txtnam.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtnam_KeyDown);
            // 
            // label3
            // 
            this.label3.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.ForeColor = System.Drawing.Color.DarkBlue;
            this.label3.Location = new System.Drawing.Point(10, 51);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(68, 23);
            this.label3.TabIndex = 6;
            this.label3.Text = "Bộ phận :";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // cbobophan
            // 
            this.cbobophan.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cbobophan.ForeColor = System.Drawing.Color.Navy;
            this.cbobophan.Location = new System.Drawing.Point(84, 53);
            this.cbobophan.Name = "cbobophan";
            this.cbobophan.Size = new System.Drawing.Size(162, 22);
            this.cbobophan.TabIndex = 2;
            this.cbobophan.SelectedIndexChanged += new System.EventHandler(this.cbobophan_SelectedIndexChanged);
            this.cbobophan.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cbobophan_KeyDown);
            this.cbobophan.TextChanged += new System.EventHandler(this.cbobophan_TextChanged);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.radKhac);
            this.groupBox1.Controls.Add(this.radBC);
            this.groupBox1.Location = new System.Drawing.Point(18, 81);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(233, 47);
            this.groupBox1.TabIndex = 3;
            this.groupBox1.TabStop = false;
            // 
            // radKhac
            // 
            this.radKhac.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radKhac.ForeColor = System.Drawing.Color.DarkBlue;
            this.radKhac.Location = new System.Drawing.Point(99, 17);
            this.radKhac.Name = "radKhac";
            this.radKhac.Size = new System.Drawing.Size(120, 24);
            this.radKhac.TabIndex = 4;
            this.radKhac.Text = "Không biên chế";
            this.radKhac.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radKhac_KeyDown);
            // 
            // radBC
            // 
            this.radBC.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radBC.ForeColor = System.Drawing.Color.DarkBlue;
            this.radBC.Location = new System.Drawing.Point(7, 17);
            this.radBC.Name = "radBC";
            this.radBC.Size = new System.Drawing.Size(82, 24);
            this.radBC.TabIndex = 3;
            this.radBC.Text = "Biên chế";
            this.radBC.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radBC_KeyDown);
            // 
            // btn_exel
            // 
            this.btn_exel.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_exel.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_exel.Image = global::Human.Properties.Resources.white_excel;
            this.btn_exel.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_exel.Location = new System.Drawing.Point(91, 144);
            this.btn_exel.Name = "btn_exel";
            this.btn_exel.Size = new System.Drawing.Size(60, 28);
            this.btn_exel.TabIndex = 6;
            this.btn_exel.Text = "&Excel";
            this.btn_exel.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_exel.Click += new System.EventHandler(this.btn_exel_Click);
            // 
            // Baocao_chamcong
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 14);
            this.ClientSize = new System.Drawing.Size(263, 183);
            this.Controls.Add(this.btn_exel);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.cbobophan);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.txtnam);
            this.Controls.Add(this.txtthang);
            this.Controls.Add(this.btn_thoat);
            this.Controls.Add(this.btn_chon);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.ForeColor = System.Drawing.Color.Navy;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "Baocao_chamcong";
            this.ShowInTaskbar = false;
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Báo cáo chấm công";
            this.Load += new System.EventHandler(this.Baocao_chamcong_Load);
            this.groupBox1.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void txtthang_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

		private void txtnam_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}{F4}");
		}

		private void Baocao_chamcong_Load(object sender, System.EventArgs e)
		{
			try
			{
                user = mDB.user;
				txtthang.Text =DateTime.Now.Month.ToString();
				txtnam.Text =DateTime.Now.Year.ToString();
				thang =txtthang.Text.Trim().PadLeft(2,'0');
				nam =txtnam.Text.Trim();
                s_mmyy = thang + nam.Substring(2, 2);
                ngay = mDB.get_ngaytrongthang(thang, nam);
                if (thang.Trim() == "01")
                {
                    p_thang = 12;
                    p_nam = Int32.Parse(nam) - 1;
                }
                tungay = "01/" + thang + "/" + nam;
                denngay = ngay + "/" + thang + "/" + nam;
                tn_thcc = mDB.tn_thcc(s_mmyy);
                dn_thcc = mDB.dn_thcc(s_mmyy);
                if (tn_thcc > 1 || dn_thcc <= Int32.Parse(ngay))
                {
                    mmyy_tr = mDB.Mmyy_truoc(s_mmyy);
                }
                ds_dmqd = mDB.get_nhom_qdcc("HC");
                ds_ca = mDB.get_lichlv();
                ds_ldo = mDB.get_dmlydo_phep();
                get_lydonghi();
                get_dsmaca();
                bvantay = mDB.bVantay(s_mmyy);       
				Load_bophan();
			}
			catch{}
        }
		private void Load_bophan()
		{
			cbobophan.DisplayMember="tenbp";
			cbobophan.ValueMember="madv";
			cbobophan.DataSource =mDB.get_bophan().Tables[0];
			cbobophan.SelectedIndex =-1;
		}
		private bool ktra(string a)
		{
			int i=0;
			try
			{
				i =int.Parse(a.Trim());
				return true;
			}
			catch
			{
				return false;
			}
		}
        
        private string d_ngay(string str)
        {
            string s = "";
            if (str.Trim().Length > 0)
            {
                string[] s1 = str.Split(',');
                for (int i = 0; i < s1.Length; i++)
                {
                    if (s1[i].ToString().Trim().IndexOf("2") == -1)
                        s = s + s1[i].ToString().Trim() + ",";
                }
            }
            return s;
        }
        private string n_ngay(string str)
        {
            string s = "";
            if (str.Trim().Length > 0)
            {
                string[] s1 = str.Split(',');
                for (int i = 0; i < s1.Length; i++)
                {
                    if (s1[i].ToString().Trim().IndexOf("2") != -1)
                        s = s + s1[i].ToString().Trim() + ",";
                }
            }
            return s;
        }
        decimal  d_cl = 0, dts_kl = 0, d_bh = 0, dts_kh = 0;
        int tsngay_lv, i = 0;
        private void tinhphep(string lydo)
        {
            //lay thong tin tong so ngay
            if (lydo.Trim() != "")
            {
                string[] s_ncl = sn_cl.Split(',');
                for (int z = 0; z < s_ncl.Length; z++)
                {
                    if (lydo == s_ncl[z].ToString().Trim())
                    {
                        if (lydo.Trim() != "CD")
                        {
                            d_cl += decimal.Parse("0.5");
                            return;
                        }
                        else
                        {
                            kt_st = 1;
                            return;
                        }
                    }
                }
                string[] s_dcl = sd_cl.Split(',');
                for (int z = 0; z < s_dcl.Length; z++)
                {
                    if (lydo == s_dcl[z].ToString().Trim())
                    {
                        if (lydo.Trim() != "CD")
                        {
                            d_cl += 1;
                            return;
                        }
                        else
                        {
                            kt_st = 1;
                            return;
                        }
                    }
                }
                string[] s_dkh = sd_kh.Split(',');
                for (int z = 0; z < s_dkh.Length; z++)
                {
                    if (lydo == s_dkh[z].ToString().Trim())
                    {
                        dts_kh += 1;
                        return;
                    }
                }
                string[] s_nkh = sn_kh.Split(',');
                for (int z = 0; z < s_nkh.Length; z++)
                {
                    if (lydo == s_nkh[z].ToString().Trim())
                    {
                        dts_kh += decimal.Parse("0.5");
                        return;
                    }
                }
                string[] s_dkl = sd_kl.Split(',');
                for (int z = 0; z < s_dkl.Length; z++)
                {
                    if (lydo == s_dkl[z].ToString().Trim())
                    {
                        if (lydo.Trim() != "KD")
                        {
                            dts_kl += 1;
                            return;
                        }
                        else
                        {
                            kt_st = 0;
                            return;
                        }
                    }
                }
                string[] s_nkl = sn_kl.Split(',');
                for (int z = 0; z < s_nkl.Length; z++)
                {
                    if (lydo == s_nkl[z].ToString().Trim())
                    {
                        if (lydo.Trim() != "KD")
                        {
                            dts_kl += decimal.Parse("0.5");
                            return;
                        }
                        else
                        {
                            kt_st = 0;
                            return;
                        }
                    }
                }
                string[] s_dbh = sd_bh.Split(',');
                for (int z = 0; z < s_dbh.Length; z++)
                {
                    if (lydo == s_dbh[z].ToString().Trim())
                    {
                        d_bh += 1;
                        return;
                    }
                }
                string[] s_nbh = sn_bh.Split(',');
                for (int z = 0; z < s_nbh.Length; z++)
                {
                    if (lydo == s_nbh[z].ToString().Trim())
                    {
                        d_bh += decimal.Parse("0.5");
                        return;
                    }
                }
            }
        }
        private DataSet get_nvchamconghc(string mmyy, int tn_thcc, int dn_thcc)
        {            
            string mn = "", n_ngay = "";
            for (int i = tn_thcc; i <= dn_thcc; i++)
            {
                n_ngay = "n" + i.ToString();
                mn += " trim(upper(" + n_ngay + "))  as m" + n_ngay + ", ";
            }
            DataSet ds = new DataSet();
            sql = " select '" + thang + "' as thang, '" + nam + "' as nam,hd.macv,a.manv,bp.madv,bp.tenbp as bophan,trim(a.ho)||' '||trim(a.ten) as hoten,c.ten,";
            if (radBC.Checked == true)
                sql += " 'Biên chế' as labl,";
            else
                if (radKhac.Checked == true)
                    sql += " 'Ngoài biên chế' as labl,";
                else
                    sql += " ' ' as labl,";
            mn = mn.Trim();
            sql += mn.Substring(0, mn.Length - 1);
            sql += " ,0.0 as cluong,0.0 as kluong,0.0 as bhiem, 0.0 as khac";
            sql += " from " + user + ".llnv a inner join " + user + ".hopdong hd on a.manv=hd.manv ";
            sql += " inner join " + user + ".chamcong_" + mmyy + " b on a.manv=b.manv";
            sql += " left join " + user + ".dmcd c on a.macd=c.ma ";
            sql += " left join " + user + ".bophan bp on bp.madv=b.madv";
            sql += " inner join " + user + ".dmcv cv on hd.macv=cv.macv";
            sql += " inner join " + user + ".mahd mh on hd.loaihd=mh.mahd ";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            sql += " where hd.ngayhd is not null and to_date(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy')<=to_date('01/" + thang + "/" + nam + "','dd/mm/yyyy') and b.mmyy='" + mmyy + "'";
            if (cbobophan.SelectedIndex > -1)
            {
                sql += "  and b.madv='" + cbobophan.SelectedValue.ToString().Trim() + "' ";
            }
            if (radBC.Checked == true)
                sql += " and mh.nhombc = 1";
            else
                if (radKhac.Checked == true)
                    sql += " and mh.nhombc <> 1";
            sql += " and hd.lan=ml.lan";
            sql += " order by bp.madv,cv.stt,c.thutu,a.manv";
            return mDB.get_data(sql);            
        }
        private void print(int prn)
        {
            DataSet dstmp = new DataSet();            
            ExportToExcel exp = new ExportToExcel();
            int mm = 0;
            s_mmyy = thang + nam.Substring(2, 2);
            string tenreport = "";
            mm = int.Parse(thang) + 1;
            if (tn_thcc > 1 && dn_thcc <= Int32.Parse(ngay))
            {
                for (int k = 1; k <= 2; k++)
                {
                    if (k == 1)
                    {
                        if (!mDB.bMmyy(mDB.Mmyy_truoc(s_mmyy)))
                        {
                            MessageBox.Show(lan.Change_language_MessageText("Không có số liệu chấm công tháng " + mDB.Mmyy_truoc(s_mmyy) + ""), AccessData.Msg, MessageBoxButtons.OK);
                        }
                        else
                        {
                            ds.Merge(get_nvchamconghc(mDB.Mmyy_truoc(s_mmyy), tn_thcc, mDB.get_ngaytrongthang(p_thang, p_nam)));
                        }
                    }
                    if(k==2)
                    {
                        ds.Merge(get_nvchamconghc(s_mmyy, 1, dn_thcc));
                    }
                }
            }
            if (tn_thcc == 1 || dn_thcc == Int32.Parse(ngay))
            {
                ds.Merge(get_nvchamconghc(s_mmyy, tn_thcc, dn_thcc));
            }
            tenreport = "h_Bangchamcong.rpt";
            ds_nv = get_thbcchamcongthang(s_mmyy);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds_nv.Tables[0].Rows)                
                {
                    DataRow[] r1 = ds.Tables[0].Select("manv='" + r["manv"].ToString().Trim() + "'", "manv asc");
                    int n_dem = 0;
                    d_cl = 0; dts_kl = 0; d_bh = 0; dts_kh = 0;

                    foreach (DataRow dr in r1)
                    {
                        if (r1.Length > 1)
                        {
                            n_dem += 1;
                        }
                        else
                        {
                            n_dem = 2;
                        }
                        if (tn_thcc > 1 && dn_thcc <= Int32.Parse(ngay))
                        {
                            for (int n = 1; n <= 2; n++)
                            {                                
                                if (mDB.bMmyy(mDB.Mmyy_truoc(s_mmyy)))
                                {
                                    if (n == 1 && n_dem ==1)
                                    {                                 
                                        for (int i = tn_thcc; i <= mDB.get_ngaytrongthang(p_thang, p_nam); i++)
                                        {
                                            string kyhieu = "";
                                            if (dr["mn" + i.ToString() + ""].ToString().Trim().Length > 2)
                                            {
                                                string[] arr = dr["mn" + i.ToString() + ""].ToString().Trim().Split('/');
                                                for (int k = 0; k <= arr.Rank; k++)
                                                {
                                                    tinhphep(arr[k].ToString().Trim());
                                                    kyhieu += get_kyhieuphep(arr[k].ToString().Trim()) + "/";
                                                }
                                                kyhieu = kyhieu.Trim().Substring(0, kyhieu.Trim().Length - 1);
                                                r["n" + i.ToString().PadLeft(2, '0') + ""] = kyhieu;
                                            }
                                            if (dr["mn" + i.ToString() + ""].ToString().Trim().Length <= 2)
                                            {
                                                tinhphep(dr["mn" + i.ToString() + ""].ToString().Trim());
                                                r["n" + i.ToString().PadLeft(2, '0') + ""] = get_kyhieuphep(dr["mn" + i.ToString() + ""].ToString().Trim());
                                            }
                                        }
                                    }
                                }
                                if (n == 2 && n_dem==2)
                                {
                                    for (int i = 1; i <= dn_thcc; i++)
                                    {
                                        string kyhieu = "";
                                        if (dr["mn" + i.ToString() + ""].ToString().Trim().Length > 2)
                                        {
                                            string[] arr = dr["mn" + i.ToString() + ""].ToString().Trim().Split('/');
                                            for (int k = 0; k <= arr.Rank; k++)
                                            {
                                                tinhphep(arr[k].ToString().Trim());
                                                kyhieu += get_kyhieuphep(arr[k].ToString().Trim()) + "/";
                                            }
                                            kyhieu = kyhieu.Trim().Substring(0, kyhieu.Trim().Length - 1);
                                            r["n" + i.ToString().PadLeft(2,'0') + ""] = kyhieu;
                                        }
                                        if (dr["mn" + i.ToString() + ""].ToString().Trim().Length <= 2)
                                        {
                                            tinhphep(dr["mn" + i.ToString() + ""].ToString().Trim());
                                            r["n" + i.ToString().PadLeft(2,'0') + ""] = get_kyhieuphep(dr["mn" + i.ToString() + ""].ToString().Trim());
                                        }
                                    }
                                }
                            }
                        }
                        if (tn_thcc == 1 || dn_thcc == Int32.Parse(ngay))
                        {
                            for (int i = tn_thcc; i <= dn_thcc; i++)
                            {
                                string kyhieu = "";
                                if (dr["mn" + i.ToString() + ""].ToString().Trim().Length > 2)
                                {
                                    string[] arr = dr["mn" + i.ToString() + ""].ToString().Trim().Split('/');
                                    for (int k = 0; k <= arr.Rank; k++)
                                    {
                                        tinhphep(arr[k].ToString().Trim());
                                        kyhieu += get_kyhieuphep(arr[k].ToString().Trim()) + "/";
                                    }
                                    kyhieu = kyhieu.Trim().Substring(0, kyhieu.Trim().Length - 1);
                                    r["n" + i.ToString().PadLeft(2,'0') + ""] = kyhieu;
                                }
                                if (dr["mn" + i.ToString() + ""].ToString().Trim().Length <= 2)
                                {
                                    tinhphep(dr["mn" + i.ToString() + ""].ToString().Trim());
                                    r["n" + i.ToString().PadLeft(2,'0') + ""] = get_kyhieuphep(dr["mn" + i.ToString() + ""].ToString().Trim());
                                }
                            }
                        }                        
                    }
                    r["t_cl"] = d_cl.ToString();
                    r["t_kl"] = dts_kl.ToString();
                    r["t_bh"] = d_bh.ToString();
                    r["t_kh"] = dts_kh.ToString();
                }                
                ds_nv.WriteXml("..\\xml\\h_bangchamcong.xml", XmlWriteMode.WriteSchema);                
                if (ds_nv.Tables[0].Rows.Count > 0)
                {
                    switch (prn)
                    {
                        case 1: frmReport f = new frmReport(mDB, ds_nv, "", tenreport, true, 0,user_dn);
                            f.ShowDialog();
                            break;
                        case 2: dstmp = exp_exel(ds);
                            exp.exp_excel_bangcc(dstmp, "bangchamcong_" + s_mmyy, thang, nam);
                            break;
                    }
                }
            }
        }
        private DataSet exp_exel(DataSet dse)
        {
            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("Hoten", typeof(string));
            string []ngay=new string[] {"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19","20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"};
            for(int i=0;i<ngay.Length;i++)
            {
                dse1.Tables[0].Columns.Add(ngay[i], typeof(string));
            }
            dse1.Tables[0].Columns.Add("tongso", typeof(string));
            dse1.Tables[0].Columns.Add("ts_lydo", typeof(string));        
            

            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[0].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt;
                nrow["Hoten"] = r["Hoten"].ToString();
                for (int i = 1; i <= ngay.Length; i++)
                {
                    string n=i.ToString().PadLeft(2,'0');
                    string m="mn"+i.ToString();
                    nrow[n] = r[m].ToString();
                }
                nrow["tongso"] = r["tongso"].ToString();
                nrow["ts_lydo"] = r["ts_lydo"].ToString();
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }
        #region TONG HOP CHAM CONG VAN TAY(lay so lieu nhan vien cham cong van tay trong thang)
        public DataSet get_dsnvchamcongt(string mmyy,string nhomca,string tn_thcc,string dn_thcc )
        {
            sql = " select l.manv,l.madv,bp.sothutu,to_char(cv.ngay,'dd/mm/yyyy') as ngaylv ";
            sql += " ,cv.tgsomtre as tg_v,cr.tgsomtre as tg_ra,cv.lydo as lydo,cr.lydo as lydo_r";
            sql += " ,cv.somtre as somtre_v,cv.tgsomtre as tgsomtre_v";
            sql += " ,cr.somtre as somtre_r,cr.tgsomtre as tgsomtre_r,to_char(cv.maca) as maca";
            sql += " from " + user + ".llnv l inner join " + mDB.user + ".ccvao_" + mmyy + " cv on l.manv=cv.manv";
            sql += " left join " + user + ".ccra_" + mmyy + " cr on l.manv=cr.manv";
            sql += " left join " + user + ".bophan bp on l.madv=bp.madv";
            sql += " inner join " + user + ".hopdong hd on l.manv=hd.manv";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";           
            sql += " where hd.lan=ml.lan and (ngaynghi is null or to_timestamp(to_char(ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') > to_timestamp('" + denngay + "','dd/mm/yyyy'))";
            sql += " and to_timestamp(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') = to_timestamp(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql += " and cv.maca=cr.maca ";
            sql += " and to_timestamp(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('"+tn_thcc+"','dd/mm/yyyy')";
            sql += " and to_timestamp(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_timestamp('" + dn_thcc + "','dd/mm/yyyy')";
            if (fields_hc.Trim() != "")
                sql += " and to_char(cv.maca) in (" + nhomca + ")";
            if (s_bophan.Trim() != "")
                sql += " and l.madv='" + s_bophan.Trim() + "'";

            sql += " union select l.manv,l.madv,bp.sothutu,";
            sql += " to_char(cn.ngay,'dd/mm/yyyy') as ngaylv,'' as tg_v,'' as tg_ra,cn.lydo as lydo,'' as lydo_r,";
            sql += " false as somtre_v,'' as tgsomtre_v,";
            sql += " false as somtre_r,'' as tgsomtre_r, cn.maca";
            sql += " from " + user + ".llnv l left join " + mDB.user + ".ccnghi_" + mmyy + " cn on l.manv=cn.manv and cn.mmyy='" + mmyy + "'";
            sql += " left join " + user + ".bophan bp on l.madv=bp.madv";
            sql += " inner join " + user + ".hopdong hd on l.manv=hd.manv";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            sql += " inner join " + user + ".mahd mh on hd.loaihd=mh.mahd ";
            sql += " where hd.lan=ml.lan and (ngaynghi is null or to_timestamp(to_char(ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') > ";
            sql += " to_timestamp('" + denngay.Trim() + "','dd/mm/yyyy')) and ngay is not null";
            sql += " and to_timestamp(to_char(cn.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + tn_thcc + "','dd/mm/yyyy')";
            sql += " and to_timestamp(to_char(cn.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_timestamp('" + dn_thcc + "','dd/mm/yyyy')";
            if (fields_hc.Trim() != "")
                sql += " and to_char(cn.maca) in (" + nhomca + ")";
            if (s_bophan.Trim() != "")
                sql += " and l.madv='" + s_bophan.Trim() + "'";
            if (radBC.Checked == true)
                sql += " and mh.nhombc = 1";
            else
                if (radKhac.Checked == true)
                    sql += " and mh.nhombc <> 1";
            sql += " order by sothutu";
            return mDB.get_data(sql);
        }
        private DataSet get_thbcchamcongthang(string mmyy)
        {
            sql = " select nv.manv,trim(nv.ho)||' '||trim(nv.ten) as hoten,nv.madv,bp.tenbp";          
            sql += " ,0.0 as t_cl,0.0 as t_kl,0.0 as t_bh,0.0 as t_kh,0.0 as sn_tru,0.0 as sn_cong";
            sql += " ,'' as n01,'' as n02,'' as n03,'' as n04,'' as n05,'' as n06,'' as n07,'' as n08,'' as n09";
            sql += " ,'' as n10,'' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17";
            sql += " ,'' as n18,'' as n19,'' as n20,'' as n21,'' as n22,'' as n23,'' as n24,'' as n25";
            sql += " ,'' as n26,'' as n27,'' as n28,'' as n29,'' as n30,'' as n31";
            sql += " ,'' as tsvao_s,'' as tsvao_t";
            sql += " ,'' as tsra_s,'' as tsra_t,'' as ghichu,'' as tsngay_lv";
            sql += " ,0.0 as m1,0.0 as m2,0.0 as m3,0.0 as tongcong";
            sql += " ,'" + mmyy + "' as mmyy,'"+thang+"' as thang,'"+nam +"' as nam";
            sql += " from " + user + ".llnv nv left join " + user + ".bophan bp on nv.madv=bp.madv";
            sql += " inner join " + user + ".hopdong hd on nv.manv=hd.manv";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            sql += " inner join " + user + ".mahd mh on hd.loaihd=mh.mahd ";
            sql += " where hd.lan=ml.lan and (ngaynghi is null or to_timestamp(to_char(ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql += " > to_timestamp('" + denngay + "','dd/mm/yyyy'))";
            if (s_bophan.Trim() != "")
                sql += " and nv.madv='" + s_bophan.Trim() + "'";
            if (radBC.Checked == true)
                sql += " and mh.nhombc = 1";
            else
                if (radKhac.Checked == true)
                    sql += " and mh.nhombc <> 1";
            sql += " order by bp.sothutu,nv.manv";
            return mDB.get_data(sql);
        }
        string f_bh = "", f_kl = "", f_cl = "", f_kh = "";
        string sd_bh = "", sd_kl = "", sd_cl = "", sd_kh = "", sn_bh = "", sn_kl = "", sn_cl = "", sn_kh = "";
        string f_hc = "", f_hn = "", f_cd = "", f_cn = "", f_tr = "", f_ng = "", f_tq = "", f_tc = "", f_khac = "", fields_hc = "", fields_hn = "", fields_cd = "", fields_cn = "", fields_tr = "", fields_ng = "", fields_tq = "", fields_tc = "", fields_khac = "";
        int kt_st = 0;//kiem tra ly do di tre ve som
        private void get_lydonghi()
        {
            string sql1 = " select case when nhombc='BH' then ma else null end as bh";
            sql1 += " ,case when nhombc='KL' then ma else null end as kl";
            sql1 += " ,case when nhombc='CL' then ma else null end as cl";
            sql1 += " ,case when nhombc='KH' then ma else null end as kh from " + mDB.user + ".dmlydo_phep";
            DataSet ds_ld = mDB.get_data(sql1);

            for (int k = 0; k < ds_ld.Tables[0].Rows.Count; k++)
            {
                if (ds_ld.Tables[0].Rows[k]["kl"].ToString().Trim() != "")
                {
                    f_kl += " " + ds_ld.Tables[0].Rows[k]["kl"].ToString().Trim() + ",";
                }
                if (ds_ld.Tables[0].Rows[k]["cl"].ToString().Trim() != "")
                {
                    f_cl += " " + ds_ld.Tables[0].Rows[k]["cl"].ToString().Trim() + ",";
                }
                if (ds_ld.Tables[0].Rows[k]["bh"].ToString().Trim() != "")
                {
                    f_bh += " " + ds_ld.Tables[0].Rows[k]["bh"].ToString().Trim() + ",";
                }
                if (ds_ld.Tables[0].Rows[k]["kh"].ToString().Trim() != "")
                {
                    f_kh += " " + ds_ld.Tables[0].Rows[k]["kh"].ToString().Trim() + ",";
                }
            }
            // lay danh sach nhom ly do phep theo nua ngay va du ngay
            sd_bh = f_bh.Length > 0 ? d_ngay(f_bh.Substring(0, f_bh.Length - 1)) : "";
            sd_kl = f_kl.Length > 0 ? d_ngay(f_kl.Substring(0, f_kl.Length - 1)) : "";
            sd_cl = f_cl.Length > 0 ? d_ngay(f_cl.Substring(0, f_cl.Length - 1)) : "";
            sd_kh = f_kh.Length > 0 ? d_ngay(f_kh.Substring(0, f_kh.Length - 1)) : "";
            sd_bh = sd_bh.Trim().Substring(0, sd_bh.Trim().Length - 1);
            sd_kl = sd_kl.Trim().Substring(0, sd_kl.Trim().Length - 1);
            sd_cl = sd_cl.Trim().Substring(0, sd_cl.Trim().Length - 1);
            sd_kh = sd_kh.Trim().Substring(0, sd_kh.Trim().Length - 1);
            //
            sn_bh = f_bh.Length > 0 ? n_ngay(f_bh.Substring(0, f_bh.Length - 1)) : "";
            sn_kl = f_kl.Length > 0 ? n_ngay(f_kl.Substring(0, f_kl.Length - 1)) : "";
            sn_cl = f_cl.Length > 0 ? n_ngay(f_cl.Substring(0, f_cl.Length - 1)) : "";
            sn_kh = f_kh.Length > 0 ? n_ngay(f_kh.Substring(0, f_kh.Length - 1)) : "";
            sn_bh = sn_bh.Trim().Substring(0, sn_bh.Trim().Length - 1);
            sn_kl = sn_kl.Trim().Substring(0, sn_kl.Trim().Length - 1);
            sn_cl = sn_cl.Trim().Substring(0, sn_cl.Trim().Length - 1);
            sn_kh = sn_kh.Trim().Substring(0, sn_kh.Trim().Length - 1);
        }
        //lay maca theo nhom ca cham cong
        private void get_dsmaca()
        {
            string sql1 = " select case when manhom='HC' then maca else null end as hc";
            sql1 += " ,case when manhom='HN' then maca else null end as hn";
            sql1 += " ,case when manhom='CD' then maca else null end as cd";
            sql1 += " ,case when manhom='CN' then maca else null end as cn";
            sql1 += " ,case when manhom='TR' then maca else null end as tr";
            sql1 += " ,case when manhom='NG' then maca else null end as ng";
            sql1 += " ,case when manhom='TQ' then maca else null end as tq ";
            sql1 += " ,case when manhom='TC' then maca else null end as tc ";
            sql1 += " ,case when manhom='KH' then maca else null end as kh from " + mDB.user + ".lichlv";
            DataSet ds_ma = mDB.get_data(sql1);

            for (int k = 0; k < ds_ma.Tables[0].Rows.Count; k++)
            {
                if (ds_ma.Tables[0].Rows[k]["hc"].ToString().Trim() != "")
                {
                    f_hc += " '" + ds_ma.Tables[0].Rows[k]["hc"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["hn"].ToString().Trim() != "")
                {
                    f_hn += " '" + ds_ma.Tables[0].Rows[k]["hn"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["cd"].ToString().Trim() != "")
                {
                    f_cd += " '" + ds_ma.Tables[0].Rows[k]["cd"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["cn"].ToString().Trim() != "")
                {
                    f_cn += " '" + ds_ma.Tables[0].Rows[k]["cn"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["tr"].ToString().Trim() != "")
                {
                    f_tr += " '" + ds_ma.Tables[0].Rows[k]["tr"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["ng"].ToString().Trim() != "")
                {
                    f_ng += " '" + ds_ma.Tables[0].Rows[k]["ng"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["tq"].ToString().Trim() != "")
                {
                    f_tq += " '" + ds_ma.Tables[0].Rows[k]["tq"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["tc"].ToString().Trim() != "")
                {
                    f_tc += " '" + ds_ma.Tables[0].Rows[k]["tc"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["kh"].ToString().Trim() != "")
                {
                    f_kh += " '" + ds_ma.Tables[0].Rows[k]["kh"].ToString().Trim() + "',";
                }
            }
            // lay danh sach nhom maca 
            fields_hc = f_hc.Length > 0 ? f_hc.Substring(0, f_hc.Length - 1) : "";
            fields_hn = f_hn.Length > 0 ? f_hn.Substring(0, f_hn.Length - 1) : "";
            fields_cd = f_cd.Length > 0 ? f_cd.Substring(0, f_cd.Length - 1) : "";
            fields_cn = f_cn.Length > 0 ? f_cn.Substring(0, f_cn.Length - 1) : "";
            fields_tr = f_tr.Length > 0 ? f_tr.Substring(0, f_tr.Length - 1) : "";
            fields_ng = f_ng.Length > 0 ? f_ng.Substring(0, f_ng.Length - 1) : "";
            fields_tq = f_tq.Length > 0 ? f_tq.Substring(0, f_tq.Length - 1) : "";
            fields_tc = f_tc.Length > 0 ? f_tc.Substring(0, f_tc.Length - 1) : "";
            fields_khac = f_khac.Length > 0 ? f_khac.Substring(0, f_khac.Length - 1) : "";
            if (fields_hc.Trim() != "")
            {
                nhomca += fields_hc;
            }
            if (fields_hn.Trim() != "")
            {
                nhomca +=","+ fields_hn;
            }
            if (fields_cd.Trim() != "")
            {
                nhomca += "," + fields_cd;
            }
            if (fields_cn.Trim() != "")
            {
                nhomca += "," + fields_cn;
            }
        }

        private string get_quydinh(int sophut)
        {
            string kyhieu = "";
            if (ds_dmqd.Tables[0].Rows.Count > 0)
            {
                int tu = 0, den = 0;
                foreach (DataRow dr in ds_dmqd.Tables[0].Rows)
                {
                    tu = int.Parse(dr["tu"].ToString());
                    den = int.Parse(dr["den"].ToString());
                    if (tu <= sophut && den >= sophut)
                    {
                        kyhieu = dr["kyhieu"].ToString();
                        break;
                    }
                }
            }
            return kyhieu;
        }
        private decimal get_muctru(string kyhieu)
        {
            decimal muctru = 0;
            if (ds_dmqd.Tables[0].Rows.Count > 0)
            {
                DataRow[] dr = ds_dmqd.Tables[0].Select("kyhieu='" + kyhieu.Trim() + "'");
                if (dr.Length > 0)
                {
                    muctru = decimal.Parse(dr[0]["songay"].ToString().Trim());
                }
            }
            return muctru;
        }
        private int get_solan(string kyhieu)
        {
            int solan = 0;
            if (ds_dmqd.Tables[0].Rows.Count > 0)
            {
                DataRow[] dr = ds_dmqd.Tables[0].Select("kyhieu='" + kyhieu.Trim() + "'");
                if (dr.Length > 0)
                {
                    solan = int.Parse(dr[0]["solan"].ToString().Trim());
                }
            }
            return solan;
        }
        private int sogio_lv(string thang, string nam)
        {
            int ngay_ct = mDB.cuoituan(thang, nam);// tong so ngay cuoi tuan trong thang (t7+ chu nhat)
            int ngay_t7 = mDB.ngaythu7(thang, nam);// ngay thu 7 trong thang
            int ngay_tt = mDB.ngaytrongthang(thang, nam);// ngay trong thang
            int sogio = (ngay_tt - ngay_ct) * 8 + ngay_t7 * 7;
            return sogio;
        }
        
        private void songay_lv(string thang, string nam)
        {
            int ngay_ct = mDB.cuoituan(thang, nam);// tong so ngay cuoi tuan trong thang (t7+ chu nhat)            
            int ngay_tt = mDB.ngaytrongthang(thang, nam);// ngay trong thang
            int ngay_t7 = mDB.ngaythu7(thang, nam);// ngay thu 7 trong thang
            tsngay_lv = (ngay_tt - ngay_ct) + ngay_t7;
        }
        private string get_nhomca(string s_maca)
        {
            string s_nhomca = "";
            DataRow[] r = ds_ca.Tables[0].Select("maca=" + s_maca.Trim() + "");
            if (r != null)
            {
                s_nhomca = r[0]["manhom"].ToString().Trim();
            }
            return s_nhomca;
        }
        private string get_kyhieuphep(string ma)
        {
            string kyhieu = "";
            if (ma.Trim() != "")
            {
                DataRow[] dr = ds_ldo.Tables[0].Select("ma = '" + ma.Trim() + "'");
                kyhieu = dr[0]["kihieu_phep"].ToString().Trim();
            }
            return kyhieu;
        }
        private void print1() //tong hop cham cong van tay
        {
            int kq1 = 0, kq2 = 0, kq3 = 0;
            kq1 = get_solan("M1");
            kq2 = get_solan("M2");
            kq3 = get_solan("M3");
            string mm = "", yyyy = "", s_ngay = "";
            mm = txtthang.Text.ToString().PadLeft(2, '0');//thang
            yyyy =txtnam.Text.ToString();
            songay_lv(mm, yyyy);
            s_ngay = mDB.get_ngaytrongthang(mm, yyyy).PadLeft(2, '0');
            if (cbobophan.SelectedIndex != -1)
                s_bophan = cbobophan.SelectedValue.ToString().Trim();
            s_mmyy = thang + nam.Substring(2, 2);
            if (mDB.bMmyy(s_mmyy))
            {
                ds_nv = get_thbcchamcongthang(s_mmyy);
            }
            if (tn_thcc > 1 && dn_thcc <= Int32.Parse(ngay))
            {
                for (int k = 1; k <= 2; k++)
                {
                    if (k == 1)
                    {
                        if (!mDB.bMmyy(mDB.Mmyy_truoc(s_mmyy)))
                        {
                            MessageBox.Show(lan.Change_language_MessageText("Không có số liệu chấm công tháng " + mDB.Mmyy_truoc(s_mmyy) + ""), AccessData.Msg, MessageBoxButtons.OK);
                        }
                        else
                        {
                            string s_tungay = tn_thcc.ToString().PadLeft(2, '0') + "/" + p_thang.ToString().PadLeft(2, '0') + "/" + p_nam.ToString();
                            string s_denngay =  mDB.get_ngaytrongthang(p_thang, p_nam) + "/" + p_thang.ToString().PadLeft(2, '0') + "/" + p_nam.ToString();
                            ds.Merge(get_dsnvchamcongt(mDB.Mmyy_truoc(s_mmyy),nhomca, s_tungay ,s_denngay));
                        }
                    }
                    if (k == 2)
                    {
                        string s_tungay = "01/" + thang.PadLeft(2, '0') + "/" + nam.ToString();
                        string s_denngay = dn_thcc.ToString().PadLeft(2,'0') + "/" + thang.PadLeft(2, '0') + "/" + nam.ToString();
                        ds.Merge(get_dsnvchamcongt(s_mmyy,nhomca,s_tungay,s_denngay));
                    }
                }
            }
            if (tn_thcc == 1 || dn_thcc == Int32.Parse(ngay))
            {
                string s_tungay = "01/" + thang.PadLeft(2, '0') + "/" + nam.ToString();
                string s_denngay = dn_thcc.ToString().PadLeft(2, '0') + "/" + thang.PadLeft(2, '0') + "/" + nam.ToString();
                ds.Merge(get_dsnvchamcongt(s_mmyy,nhomca,s_tungay,s_denngay));
            }

            if (ds_nv.Tables[0].Rows.Count > 0)
            {
                string ngay_lv = "";
                foreach (DataRow dr_nv in ds_nv.Tables[0].Rows)
                {
                    int pts_vs = 0, pts_vt = 0, pts_rs = 0, pts_rt = 0, m1 = 0, m2 = 0, m3 = 0;// 
                    d_cl = 0; d_bh = 0; dts_kl = 0; dts_kh = 0;
                    DataRow[] dr = ds.Tables[0].Select("manv='" + dr_nv["manv"].ToString().Trim() + "'");
                    string s_vao = "", s_ra = "", s_dong = "";
                    string f_ngay = "";
                    decimal ts_ngaycc = 0, sn_tru = 0, sn_cong = 0;
                    if (dr.Length > 0)
                    {
                        f_ngay = dr[0]["ngaylv"].ToString().Substring(0, 2);                       
                    }
                    foreach (DataRow r in dr)
                    {
                        int pv_s = 0, pv_t = 0, pr_s = 0, pr_t = 0;
                        int h_vao = 0, p_vao = 0, h_ra = 0, p_ra = 0;
                        string khieu = "";
                        if (r["ngaylv"].ToString().Trim() != "")
                        {
                            if (f_ngay != r["ngaylv"].ToString().Substring(0, 2))
                            {
                                f_ngay = r["ngaylv"].ToString().Substring(0, 2);
                                s_dong = ""; s_vao = ""; s_ra = "";
                            }
                            ngay_lv = r["ngaylv"].ToString();
                            if ((r["lydo"].ToString().Trim() == "") || (r["lydo"].ToString().Trim() != "" && r["tg_v"].ToString().Trim() != "" && r["tg_ra"].ToString().Trim() != ""))
                            {
                                tinhphep(r["lydo"].ToString().Trim());
                                if (get_nhomca(r["maca"].ToString().Trim()) == "HC" || get_nhomca(r["maca"].ToString().Trim()) == "CD")
                                {
                                    ts_ngaycc += 1;
                                }
                                if (get_nhomca(r["maca"].ToString().Trim()) == "HN" || get_nhomca(r["maca"].ToString().Trim()) == "CN")
                                {
                                    ts_ngaycc += decimal.Parse("0.5");
                                }
                                int doirap_v = 0, doirap_r = 0;
                                bool somtre_v = false, somtre_r = false;
                                string ccv = "", ccr = "";
                                ccv = r["tgsomtre_v"].ToString();
                                ccr = r["tgsomtre_r"].ToString();
                                string[] g_vao; string[] g_ra;
                                string manv = r["manv"].ToString().Trim();
                                if (ccv.Trim() != "" && ccr.Trim() != "")
                                {
                                    g_vao = ccv.Split(':'); g_ra = ccr.Split(':');
                                    h_vao = int.Parse(g_vao[0].ToString());
                                    p_vao = int.Parse(g_vao[1].ToString());
                                    h_ra = int.Parse(g_ra[0].ToString());
                                    p_ra = int.Parse(g_ra[1].ToString());
                                }
                                else
                                {
                                    h_vao = 0;
                                    p_vao = 0;
                                    h_ra = 0;
                                    p_ra = 0;
                                }
                                somtre_v = bool.Parse(r["somtre_v"].ToString());
                                somtre_r = bool.Parse(r["somtre_r"].ToString());
                                doirap_v = h_vao * 60 + p_vao;
                                doirap_r = h_ra * 60 + p_ra;
                                //DateTime ngay = Convert.ToDateTime(ngay_lv.Substring(3, 3) + ngay_lv.Substring(0, 3) + ngay_lv.Substring(6, 4));
                                if (!somtre_v)
                                {
                                    pv_s = doirap_v;
                                    pts_vs += pv_s;
                                }
                                if (somtre_v)
                                {
                                    pv_t = doirap_v;
                                    pts_vt += pv_t;
                                }
                                if (somtre_r)
                                {
                                    pr_s = doirap_r;
                                    pts_rs += pr_s;
                                }
                                if (!somtre_r)
                                {
                                    pr_t = doirap_r;
                                    pts_rt += pr_t;
                                }
                                // ky hieu hien thi thoi gian vao
                                if (pv_s > pv_t)
                                {
                                    s_vao = "+" + pv_s.ToString().PadLeft(2, '0');
                                }
                                if (pv_s < pv_t)
                                {
                                    s_vao = "-" + pv_t.ToString().PadLeft(2, '0');
                                    khieu = get_quydinh(pv_t);
                                    if (kt_st == 0)
                                    {
                                        if (khieu == "M1")
                                        {
                                            m1++;
                                        }
                                        if (khieu == "M2")
                                        {
                                            m2++;
                                        }
                                        if (khieu == "M3")
                                        {
                                            m3++;
                                        }
                                    }
                                }
                                if (pv_s == pv_t)
                                {
                                    s_vao = pv_t.ToString().PadLeft(2, '0');
                                }
                                // ky hieu hien thi thoi gian ra 
                                if (pr_s > pr_t)
                                {
                                    s_ra = "-" + pr_s.ToString().PadLeft(2, '0');
                                    khieu = get_quydinh(pr_s);
                                    if (kt_st == 0)
                                    {
                                        if (khieu == "M1")
                                        {
                                            m1++;
                                        }
                                        if (khieu == "M2")
                                        {
                                            m2++;
                                        }
                                        if (khieu == "M3")
                                        {
                                            m3++;
                                        }
                                    }
                                }
                                if (pr_s < pr_t)
                                {
                                    s_ra = "+" + pr_t.ToString().PadLeft(2, '0');
                                }
                                if (pr_s == pr_t)
                                {
                                    s_ra = pr_s.ToString().PadLeft(2, '0');
                                }
                                s_dong += s_vao + ":" + s_ra + " ";
                            }
                            else
                            {
                                s_dong += "  " + get_kyhieuphep(r["lydo"].ToString()) + " ";
                                tinhphep(r["lydo"].ToString().Trim());
                            }
                            dr_nv["n" + r["ngaylv"].ToString().Substring(0, 2) + ""] = s_dong;
                        }
                    }
                    // gan du lieu tong thoi gian vao som tre trong thang cua nhan vien
                    dr_nv["tsvao_s"] = (pts_vs / 60).ToString().PadLeft(2, '0') + ":" + (pts_vs % 60).ToString().PadLeft(2, '0');
                    dr_nv["tsvao_t"] = (pts_vt / 60).ToString().PadLeft(2, '0') + ":" + (pts_vt % 60).ToString().PadLeft(2, '0');
                    dr_nv["tsra_s"] = (pts_rs / 60).ToString().PadLeft(2, '0') + ":" + (pts_rs % 60).ToString().PadLeft(2, '0');
                    dr_nv["tsra_t"] = (pts_rt / 60).ToString().PadLeft(2, '0') + ":" + (pts_rt % 60).ToString().PadLeft(2, '0');
                    dr_nv["t_kh"] = dts_kh.ToString();
                    dr_nv["t_kl"] = dts_kl.ToString();
                    dr_nv["t_bh"] = d_bh.ToString();
                    dr_nv["m1"] = m1.ToString();
                    dr_nv["m2"] = m2.ToString();
                    dr_nv["m3"] = m3.ToString();
                    if (kq1 > 0 && kq2 > 0 && kq2 > 0)
                    {
                        sn_tru = (m1 / kq1 * get_muctru("M1")) + (m2 / kq2 * get_muctru("M2")) + (m3 / kq3 * get_muctru("M3"));
                    }
                    dr_nv["sn_tru"] = sn_tru.ToString();
                    dr_nv["t_cl"] = (ts_ngaycc + d_cl).ToString();
                    dr_nv["sn_cong"] = sn_cong.ToString();
                    dr_nv["tongcong"]=((ts_ngaycc + d_cl) - sn_tru).ToString();
                    dr_nv["tsngay_lv"] = tsngay_lv.ToString();
                    i++;
                }
                ds_nv.WriteXml("..\\xml\\h_bangchamcongvt.xml", XmlWriteMode.WriteSchema);
                if (ds_nv.Tables[0].Rows.Count > 0)
                {
                    frmReport f = new frmReport(mDB, ds_nv, "", "h_bangchamcongvt.rpt", true, 0,user_dn);
                    f.ShowDialog();
                }                
            }
            else
                    MessageBox.Show("Không có số liệu!");
        }
        
        #endregion

        private void btn_exel_Click(object sender, EventArgs e)
        {
            if (!ktra(txtthang.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Tháng không hợp lệ!"),lan.Change_language_MessageText( "Thông báo"));
                txtthang.Focus();
                return;
            }
            if (!ktra(txtnam.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Năm không hợp lệ!"),lan.Change_language_MessageText( "Thông báo"));
                txtnam.Focus();
                return;
            }
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            if (!mDB.bMmyy(thang + nam.Substring(2, 2)))
            {
                MessageBox.Show(lan.Change_language_MessageText("Số liệu chưa tạo!"),lan.Change_language_MessageText( "Thông báo"));
                return;
            }
            print(2);
            btn_thoat.Focus();
        }

        private void btn_chon_Click(object sender, EventArgs e)
        {
            if (!ktra(txtthang.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Tháng không hợp lệ!"),lan.Change_language_MessageText( "Thông báo"));
                txtthang.Focus();
                return;
            }
            if (!ktra(txtnam.Text.Trim()))
            {
                MessageBox.Show(lan.Change_language_MessageText("Năm không hợp lệ!"),lan.Change_language_MessageText( "Thông báo"));
                txtnam.Focus();
                return;
            }
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            if (!mDB.bMmyy(thang + nam.Substring(2, 2)))
            {
                MessageBox.Show(lan.Change_language_MessageText("Số liệu chưa tạo !"),lan.Change_language_MessageText( "Thông báo"));
                return;
            }
            s_mmyy = thang + nam.Substring(2, 2);
            ngay = mDB.get_ngaytrongthang(thang, nam);
            tn_thcc = mDB.tn_thcc(s_mmyy);
            dn_thcc = mDB.dn_thcc(s_mmyy);
            if (thang.Trim() == "01")
            {
                p_thang = 12;
                p_nam = Int32.Parse(nam) - 1;
            }
            else
            {
                p_thang = Int32.Parse(thang) - 1;
                p_nam = Int32.Parse(nam);
            }
            if (tn_thcc > 1 || dn_thcc <= Int32.Parse(ngay))
            {
                mmyy_tr = mDB.Mmyy_truoc(s_mmyy);
            }

            if (bvantay)
            {
                print1();
                ds.Clear();
            }
            else
            {
                print(1);
                ds.Clear();
            }
            btn_exel.Focus();
        }

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void cbobophan_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");          
        }

        private void radBC_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void radKhac_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void cbobophan_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.ActiveControl == cbobophan)
            {
                if (cbobophan.SelectedIndex != -1)
                {
                    s_bophan = cbobophan.SelectedValue.ToString();
                    bvantay = mDB.bVantay(s_mmyy);
                }
            }
        }

        private void txtthang_Validated(object sender, EventArgs e)
        {
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            s_mmyy = thang + nam.Substring(2, 2);
            ngay = mDB.get_ngaytrongthang(thang, nam);
            tungay = "01/" + thang + "/" + nam;
            denngay = ngay + "/" + thang + "/" + nam;
            bvantay = mDB.bVantay(s_mmyy);   
        }

        private void txtnam_Validated(object sender, EventArgs e)
        {
            thang = txtthang.Text.Trim().PadLeft(2, '0');
            nam = txtnam.Text.Trim();
            s_mmyy = thang + nam.Substring(2, 2);
            ngay = mDB.get_ngaytrongthang(thang, nam);
            tungay = "01/" + thang + "/" + nam;
            denngay = ngay + "/" + thang + "/" + nam;
            bvantay = mDB.bVantay(s_mmyy);   
        }

        private void cbobophan_TextChanged(object sender, EventArgs e)
        {
            if (cbobophan.Text == "")
                s_bophan = "";
        }      
	}
}
